package achievement.dao.Impl;

import achievement.dao.SzlBookDao;
import achievement.domain.SzlBook;
import cn.hutool.core.util.StrUtil;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class


SzlBookDaoImpl implements SzlBookDao {
    private Connection con;
    private PreparedStatement psmt;
    private ResultSet rs;
    @Override
    public void insert(SzlBook szlBook) {
        String sql = "insert into szl_book\n" +
                "        (book_name,book_publish,book_time,book_rank,book_proof) \n" +
                "values(?,?,?,?,?)";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1,szlBook.getBookName());
            psmt.setString(2,szlBook.getBookPublish());
            psmt.setObject(3,szlBook.getBookTime());
            psmt.setInt(4,szlBook.getBookRank());
            psmt.setString(5,szlBook.getBookProof());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void updateById(SzlBook szlBook) {
        String sql = "update szl_book set";
        List<Object> param = new ArrayList<>();
        if (szlBook == null){
            return;
        }else{
            if(szlBook.getBookId() != null){
                sql += " book_id = ?,";
                param.add(szlBook.getBookId());
            }
            if(szlBook.getBookName() != null){
                sql += " book_name = ?,";
                param.add(szlBook.getBookName());
            }
            if(szlBook.getType() != null){
                sql += " type = ?,";
                param.add(szlBook.getType());
            }
            if(szlBook.getBookPublish() != null){
                sql += " book_publish = ?,";
                param.add(szlBook.getBookPublish());
            }
            if(szlBook.getBookTime() != null){
                sql += " book_time = ?,";
                param.add(szlBook.getBookTime());
            }
            if(szlBook.getBookRank() != null){
                sql += " book_rank = ?,";
                param.add(szlBook.getBookRank());
            }
            if(szlBook.getBookProof() != null){
                sql += " book_proof = ?,";
                param.add(szlBook.getBookProof());
            }
        }
        sql = StrUtil.strip(sql,null,",");
        sql+=" where book_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            psmt.setLong(param.size()+1,szlBook.getBookId());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from szl_book where book_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public SzlBook selectById(Long id) {
        String sql = "select * from szl_book where book_id = ?";
        SzlBook szlBook = new SzlBook();
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            rs = psmt.executeQuery();
            while (rs.next()){
                szlBook.setBookId(rs.getLong(1));
                szlBook.setBookName(rs.getString(2));
                szlBook.setType(rs.getInt(3));
                szlBook.setBookPublish(rs.getString(4));
                szlBook.setBookTime(rs.getDate(5));
                szlBook.setBookRank(rs.getInt(6));
                szlBook.setBookProof(rs.getString(7));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return szlBook;
    }

    @Override
    public List<SzlBook> selectList(SzlBook szlBook) {
        String sql = "select * from szl_book where 1=1";
        List<SzlBook> list = new ArrayList<>();
        List<Object> param = new ArrayList<>();
        if (szlBook == null){
            sql = "select * from szl_book";
        }else{
            if(szlBook.getBookId() != null){
                sql += " and book_id = ?";
                param.add(szlBook.getBookId());
            }
            if(szlBook.getBookName() != null){
                sql += " and book_name like \"%\"?\"%\""; //模糊查询
                param.add(szlBook.getBookName());
            }
            if(szlBook.getType() != null){
                sql += " and type = ?";
                param.add(szlBook.getType());
            }
            if(szlBook.getBookPublish() != null){
                sql += " and book_publish like \"%\"?\"%\"";
                param.add(szlBook.getBookPublish());
            }
            if(szlBook.getBookTime() != null){
                sql += " and book_time = ?";
                param.add(szlBook.getBookTime());
            }
            if(szlBook.getBookRank() != null){
                sql += " and book_rank = ?";
                param.add(szlBook.getBookRank());
            }
            if(szlBook.getBookProof() != null){
                sql += " and book_proof like \"%\"?\"%\"";
                param.add(szlBook.getBookProof());
            }
        }
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            rs = psmt.executeQuery();
            while (rs.next()){
                SzlBook szlBook1 = new SzlBook();
                szlBook1.setBookId(rs.getLong(1));
                szlBook1.setBookName(rs.getString(2));
                szlBook1.setType(rs.getInt(3));
                szlBook1.setBookPublish(rs.getString(4));
                szlBook1.setBookTime(rs.getDate(5));
                szlBook1.setBookRank(rs.getInt(6));
                szlBook1.setBookProof(rs.getString(7));
                list.add(szlBook1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return list ;
    }
}
